﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Reflection;
using DocumentFormat.OpenXml.Packaging;
using DocumentFormat.OpenXml.Spreadsheet;
using DocumentFormat.OpenXml;
using System.IO;
using A = DocumentFormat.OpenXml.Drawing;
using Xdr = DocumentFormat.OpenXml.Drawing.Spreadsheet;
using A14 = DocumentFormat.OpenXml.Office2010.Drawing;
using System.Drawing;
using System.Drawing.Imaging;

namespace OEMS
{
    public class Export 
    {
        #region Algemeen
        

        /// <summary>
        /// Geeft de parameters door aan de exportmethods, afhankelijk van het bestandstype.
        /// </summary>
        /// <param name="filePath">Bestandspad</param>
        /// <param name="motor">Motorobject</param>
        /// <param name="fileType">Bestandstype</param>
        /// <param name="grafiek">Boolean die bepaalt of de grafiek ook moet worden geëxporteerd</param>
        /// <author>Ronald Geladé</author>
        public static void ToFile(string filePath, Motor motor, string fileType, Boolean grafiek)
        {
            if (fileType.Equals("pdf"))
            {
                ExportPdf.ToPdf(filePath, motor, grafiek);
            }
            else if (fileType.Equals("excel"))
            {
                List<Meting> metingList = MetingDB.GetMetingen(motor.GegevensId);

                CreateExcelFile(filePath, metingList, grafiek, null,motor);
            }
        }

        public static string setFileName(int ListBoxIndex, String onderdeel, List<Motor> motorList, List<Belasting> belastingList, List<Overbrenging> overbrengingList)
        {
            String fileName = "";
            if (onderdeel.Equals("Motor"))
            {
                String nomVermogen = Convert.ToString(motorList[ListBoxIndex].NominaalVermogen);

                int kommaIndex = nomVermogen.IndexOf(",");

                if ((nomVermogen.Substring(kommaIndex + 1, 1).Equals("0") && (nomVermogen.Substring(kommaIndex + 2, 1).Equals("0"))))
                {
                    nomVermogen = nomVermogen.Substring(0, kommaIndex);
                }
                else
                {
                    nomVermogen = nomVermogen.Substring(0, kommaIndex + 3);
                }
                fileName = (nomVermogen + "-" + Convert.ToInt32(motorList[ListBoxIndex].NominaalToerental) + "-" + motorList[ListBoxIndex].Klasse).ToString();

            }
            return fileName;
        }


        #endregion
        
        #region ExcelExport

        public static string chartImage = @"..\..\chart\pic.bmp";
        
        /// <summary>
        /// Excel file aanmaken
        /// </summary>
        /// <param name="filePath"></param>
        /// <param name="metingList"></param>
        /// <param name="grafiek"></param>
        /// <param name="image"></param>
        /// <author>Robin Goris</author>
        /// 
        public static void CreateExcelFile(string filePath, List<Meting> metingList, Boolean grafiek, Image image,Motor motor)
        {
            
            OptimaalPunt op = new OptimaalPunt(motor);
            String[] optimaleNamen = new String[] { "Koppel:", "Toerental:", "Rendement:" };
            Decimal[] optimalePtn = new Decimal[] { op.Koppel, op.Toerental, op.Rendement };

            using (SpreadsheetDocument ssDoc = SpreadsheetDocument.Create(filePath, SpreadsheetDocumentType.Workbook, true))
            {
                WorkbookPart workbookPart = ssDoc.AddWorkbookPart();
                workbookPart.Workbook = new Workbook();
                
                for (int i = 1; i <= 4; i++)
                {
                    WorksheetPart worksheetPart = workbookPart.AddNewPart<WorksheetPart>();
                    worksheetPart.Worksheet = new Worksheet(new SheetData());

                    Sheets sheets = ssDoc.WorkbookPart.Workbook.AppendChild<Sheets>(new Sheets());
                    String sheetName = "";
                    if (i == 1)
                    {
                        sheetName = "Toerental";

                    }
                    else if (i == 2)
                    {
                        sheetName = "Koppel";

                    }
                    else if (i == 3)
                    {
                        sheetName = "Rendement";

                    }
                    else if (i == 4)
                    {
                        sheetName = "Optimale punten + Grafiek";

                    }

                    Sheet sheet = new Sheet()
                    {
                        Id = ssDoc.WorkbookPart.GetIdOfPart(worksheetPart),
                        SheetId = 1,

                        Name = sheetName
                    };

                    sheets.Append(sheet);

                    Worksheet worksheet = new Worksheet();
                    SheetData sheetData = new SheetData();
                    int teller = 0;
                    for (int t = 0; t < 42; t++)
                    {
                        Row row = new Row();
                        for (int j = 0; j < 42; j++)
                        {
                            if (i == 1)
                            {
                                Cell mycell = CreateTextCell(GetExcelColumnName(j), t+1, metingList[teller].Toerental.ToString());
                                row.Append(mycell);
                            }
                            if (i == 2)
                            {
                                Cell mycell = CreateTextCell(GetExcelColumnName(j), t+1, metingList[teller].Moment.ToString());
                                row.Append(mycell);
                            }
                            if (i == 3)
                            {
                                Cell mycell = CreateTextCell(GetExcelColumnName(j), t+1, metingList[teller].Rendement.ToString());
                                row.Append(mycell);
                            }
  
                            teller++;
                        }

                        sheetData.Append(row);
                    }
                    if (i == 4)
                    {
       
                        for (int y = 0; y < 3; y++)
                        {

                            Row newrow = new Row();
                            Cell cell = CreateTextCell(GetExcelColumnName(0), y+1, optimaleNamen[y]);
                            newrow.Append(cell);                           
                            sheetData.Append(newrow);
                            newrow = new Row();
                            cell = CreateTextCell(GetExcelColumnName(1), y+1, optimalePtn[y].ToString());
                            newrow.Append(cell);
                            sheetData.Append(newrow);
                        }




                    }

                    worksheet.Append(sheetData);

                    worksheetPart.Worksheet = worksheet;

                    if (i == 4)
                    {

                        InsertImage(worksheetPart, 4, 4, 35, 20, new FileStream(chartImage, FileMode.Open));

                    }
                }

                ssDoc.Close();
            }
        }

        /// <summary>
        /// Cell aanmaken
        /// </summary>
        /// <param name="excelColumnName"></param>
        /// <param name="excelRowIndex"></param>
        /// <param name="cellValue"></param>
        /// <returns></returns>
        
        private static Cell CreateTextCell(string excelColumnName, int excelRowIndex, string cellValue)
        {
            
            Cell c = new Cell();
            c.DataType = CellValues.InlineString;
            c.CellReference = excelColumnName + excelRowIndex.ToString();

            InlineString inlineString = new InlineString();
            Text t = new Text();
            t.Text = cellValue.ToString();
            inlineString.AppendChild(t);
            c.AppendChild(inlineString);

            return c;
        }
        /// <summary>
        /// index nr van kolom omzetten naar excel kolom naam... A B C
        /// </summary>
        /// <param name="columnIndex"></param>
        /// <returns></returns>
        /// <author>Robin Goris</author>
        private static string GetExcelColumnName(int columnIndex)
        {
            if (columnIndex < 26)
                return ((char)('A' + columnIndex)).ToString();

            char firstChar = (char)('A' + (columnIndex / 26));
            char secondChar = (char)('A' + (columnIndex % 26));

            return string.Format("{0}{1}", firstChar, secondChar);
        }

        /// <summary>
        /// Chart invoegen in cheet
        /// </summary>
        /// <param name="sheet1"></param>
        /// <param name="startRowIndex"></param>
        /// <param name="startColumnIndex"></param>
        /// <param name="endRowIndex"></param>
        /// <param name="endColumnIndex"></param>
        /// <param name="imageStream"></param>
        private static void InsertImage(WorksheetPart sheet1, int startRowIndex, int startColumnIndex, int endRowIndex, int endColumnIndex, Stream imageStream)
        {
            /// <summary>
            /// Inserts the image at the specified location 
            /// </summary>
            /// <param name="sheet1">The WorksheetPart where image to be inserted</param>
            /// <param name="startRowIndex">The starting Row Index</param>
            /// <param name="startColumnIndex">The starting column index</param>
            /// <param name="endRowIndex">The ending row index</param>
            /// <param name="endColumnIndex">The ending column index</param>
            /// <param name="imageStream">Stream which contains the image data</param>

            //Inserting a drawing element in worksheet
            //Make sure that the relationship id is same for drawing element in worksheet and its relationship part
            int drawingPartId = GetNextRelationShipID(sheet1);
            Drawing drawing1 = new Drawing() { Id = "rId" + drawingPartId.ToString() };

            //Check whether the WorksheetPart contains VmlDrawingParts (LegacyDrawing element)
            if (sheet1.VmlDrawingParts == null)
            {
                //if there is no VMLDrawing part (LegacyDrawing element) exists, just append the drawing part to the sheet
                sheet1.Worksheet.Append(drawing1);
            }
            else
            {
                //if VmlDrawingPart (LegacyDrawing element) exists, then find the index of legacy drawing in the sheet and inserts the new drawing element before VMLDrawing part
                int legacyDrawingIndex = GetIndexofLegacyDrawing(sheet1);
                if (legacyDrawingIndex != -1)
                    sheet1.Worksheet.InsertAt<OpenXmlElement>(drawing1, legacyDrawingIndex);
                else
                    sheet1.Worksheet.Append(drawing1);
            }
            //Adding the drawings.xml part
            DrawingsPart drawingsPart1 = sheet1.AddNewPart<DrawingsPart>("rId" + drawingPartId.ToString());
            GenerateDrawingsPart1Content(drawingsPart1, startRowIndex, startColumnIndex, endRowIndex, endColumnIndex);
            //Adding the image
            ImagePart imagePart1 = drawingsPart1.AddNewPart<ImagePart>("image/jpeg", "rId1");
            imagePart1.FeedData(imageStream);
        }
        #region Image insert Help methods
        /// <summary>
        /// Get the index of legacy drawing element in the specified WorksheetPart
        /// </summary>
        /// <param name="sheet1">The worksheetPart</param>
        /// <returns>Index of legacy drawing</returns>
        private static int GetIndexofLegacyDrawing(WorksheetPart sheet1)
        {
            for (int i = 0; i < sheet1.Worksheet.ChildElements.Count; i++)
            {
                OpenXmlElement element = sheet1.Worksheet.ChildElements[i];
                if (element is LegacyDrawing)
                    return i;
            }
            return -1;
        }
        /// <summary>
        /// Returns the WorksheetPart for the specified sheet name
        /// </summary>
        /// <param name="workbookpart">The WorkbookPart</param>
        /// <param name="sheetName">The name of the worksheet</param>
        /// <returns>Returns the WorksheetPart for the specified sheet name</returns>
        private static WorksheetPart GetSheetByName(WorkbookPart workbookpart, string sheetName)
        {
            foreach (WorksheetPart sheetPart in workbookpart.WorksheetParts)
            {
                string uri = sheetPart.Uri.ToString();
                if (uri.EndsWith(sheetName + ".xml"))
                    return sheetPart;
            }
            return null;
        }
        /// <summary>
        /// Returns the next relationship id for the specified WorksheetPart
        /// </summary>
        /// <param name="sheet1">The worksheetPart</param>
        /// <returns>Returns the next relationship id </returns>
        private static int GetNextRelationShipID(WorksheetPart sheet1)
        {
            int nextId = 0;
            List<int> ids = new List<int>();
            foreach (IdPartPair part in sheet1.Parts)
            {
                ids.Add(int.Parse(part.RelationshipId.Replace("rId", string.Empty)));
            }
            if (ids.Count > 0)
                nextId = ids.Max() + 1;
            else
                nextId = 1;
            return nextId;
        }

        // Generates content of drawingsPart1.
        private static void GenerateDrawingsPart1Content(DrawingsPart drawingsPart1, int startRowIndex, int startColumnIndex, int endRowIndex, int endColumnIndex)
        {
            Xdr.WorksheetDrawing worksheetDrawing1 = new Xdr.WorksheetDrawing();
            worksheetDrawing1.AddNamespaceDeclaration("xdr", "http://schemas.openxmlformats.org/drawingml/2006/spreadsheetDrawing");
            worksheetDrawing1.AddNamespaceDeclaration("a", "http://schemas.openxmlformats.org/drawingml/2006/main");

            Xdr.TwoCellAnchor twoCellAnchor1 = new Xdr.TwoCellAnchor() { EditAs = Xdr.EditAsValues.OneCell };

            Xdr.FromMarker fromMarker1 = new Xdr.FromMarker();
            Xdr.ColumnId columnId1 = new Xdr.ColumnId();
            columnId1.Text = startColumnIndex.ToString();
            Xdr.ColumnOffset columnOffset1 = new Xdr.ColumnOffset();
            columnOffset1.Text = "38100";
            Xdr.RowId rowId1 = new Xdr.RowId();
            rowId1.Text = startRowIndex.ToString();
            Xdr.RowOffset rowOffset1 = new Xdr.RowOffset();
            rowOffset1.Text = "0";

            fromMarker1.Append(columnId1);
            fromMarker1.Append(columnOffset1);
            fromMarker1.Append(rowId1);
            fromMarker1.Append(rowOffset1);

            Xdr.ToMarker toMarker1 = new Xdr.ToMarker();
            Xdr.ColumnId columnId2 = new Xdr.ColumnId();
            columnId2.Text = endColumnIndex.ToString();
            Xdr.ColumnOffset columnOffset2 = new Xdr.ColumnOffset();
            columnOffset2.Text = "542925";
            Xdr.RowId rowId2 = new Xdr.RowId();
            rowId2.Text = endRowIndex.ToString();
            Xdr.RowOffset rowOffset2 = new Xdr.RowOffset();
            rowOffset2.Text = "161925";

            toMarker1.Append(columnId2);
            toMarker1.Append(columnOffset2);
            toMarker1.Append(rowId2);
            toMarker1.Append(rowOffset2);

            Xdr.Picture picture1 = new Xdr.Picture();

            Xdr.NonVisualPictureProperties nonVisualPictureProperties1 = new Xdr.NonVisualPictureProperties();
            Xdr.NonVisualDrawingProperties nonVisualDrawingProperties1 = new Xdr.NonVisualDrawingProperties() { Id = (UInt32Value)2U, Name = "Picture 1" };

            Xdr.NonVisualPictureDrawingProperties nonVisualPictureDrawingProperties1 = new Xdr.NonVisualPictureDrawingProperties();
            A.PictureLocks pictureLocks1 = new A.PictureLocks() { NoChangeAspect = true };

            nonVisualPictureDrawingProperties1.Append(pictureLocks1);

            nonVisualPictureProperties1.Append(nonVisualDrawingProperties1);
            nonVisualPictureProperties1.Append(nonVisualPictureDrawingProperties1);

            Xdr.BlipFill blipFill1 = new Xdr.BlipFill();

            A.Blip blip1 = new A.Blip() { Embed = "rId1", CompressionState = A.BlipCompressionValues.Print };
            blip1.AddNamespaceDeclaration("r", "http://schemas.openxmlformats.org/officeDocument/2006/relationships");

            A.BlipExtensionList blipExtensionList1 = new A.BlipExtensionList();

            A.BlipExtension blipExtension1 = new A.BlipExtension() { Uri = "{28A0092B-C50C-407E-A947-70E740481C1C}" };

            A14.UseLocalDpi useLocalDpi1 = new A14.UseLocalDpi() { Val = false };
            useLocalDpi1.AddNamespaceDeclaration("a14", "http://schemas.microsoft.com/office/drawing/2010/main");

            blipExtension1.Append(useLocalDpi1);

            blipExtensionList1.Append(blipExtension1);

            blip1.Append(blipExtensionList1);

            A.Stretch stretch1 = new A.Stretch();
            A.FillRectangle fillRectangle1 = new A.FillRectangle();

            stretch1.Append(fillRectangle1);

            blipFill1.Append(blip1);
            blipFill1.Append(stretch1);

            Xdr.ShapeProperties shapeProperties1 = new Xdr.ShapeProperties();

            A.Transform2D transform2D1 = new A.Transform2D();
            A.Offset offset1 = new A.Offset() { X = 1257300L, Y = 762000L };
            A.Extents extents1 = new A.Extents() { Cx = 2943225L, Cy = 2257425L };

            transform2D1.Append(offset1);
            transform2D1.Append(extents1);

            A.PresetGeometry presetGeometry1 = new A.PresetGeometry() { Preset = A.ShapeTypeValues.Rectangle };
            A.AdjustValueList adjustValueList1 = new A.AdjustValueList();

            presetGeometry1.Append(adjustValueList1);

            shapeProperties1.Append(transform2D1);
            shapeProperties1.Append(presetGeometry1);

            picture1.Append(nonVisualPictureProperties1);
            picture1.Append(blipFill1);
            picture1.Append(shapeProperties1);
            Xdr.ClientData clientData1 = new Xdr.ClientData();

            twoCellAnchor1.Append(fromMarker1);
            twoCellAnchor1.Append(toMarker1);
            twoCellAnchor1.Append(picture1);
            twoCellAnchor1.Append(clientData1);

            worksheetDrawing1.Append(twoCellAnchor1);

            drawingsPart1.WorksheetDrawing = worksheetDrawing1;
        }
        #endregion 

        #endregion
    }
}
